package cs341.group2.mycontacts.Database;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteDatabase.CursorFactory;
import android.database.sqlite.SQLiteOpenHelper;
import android.os.AsyncTask;

public class MyContactsDB {
	public static final String DATABASE_NAME = "MyContact";
	public static final int SCHEMA_VERSION = 1;
	
	public static final String DATABASE_TABLE_CATEGORY = "" +
			"CREATE TABLE Category ("
				+ "Id integer primary key autoincrement,"
				+ "Name varchar(25) NOT NULL"
			+ ");";
	
	public static final String DATABASE_TABLE_CONTACT = "" +
			"CREATE TABLE Contact ("
				+ "Id integer primary key autoincrement, "
				+ "Name varchar(50) NOT NULL,"
				+ "Address varchar(50),"
				+ "CityZip varchar(50),"
				+ "CellPhone varchar(15) NULL NULL,"
				+ "AltPhone varchar(15),"
				+ "Email varchar(30),"
				+ "Comments varchar(200),"
				+ "CategoryId int,"
				+ "foreign key (CategoryId) references Category"
			+ "	);";
	
	private Context context;
	private DbHelper helper;
	private SQLiteDatabase database;
	
	private final class DbHelper extends SQLiteOpenHelper{

		public DbHelper(Context context) {
			super(context, DATABASE_NAME, null, SCHEMA_VERSION);
			// TODO Auto-generated constructor stub
		}

		@Override
		public void onCreate(SQLiteDatabase db) {
			// TODO Auto-generated method stub
			db.execSQL(DATABASE_TABLE_CATEGORY);
			db.execSQL(DATABASE_TABLE_CONTACT);
			//insert the default categories
			ContentValues values = new ContentValues();
			for(int i = 1; i <= 5; i++){
				values.put("Name", "Category" + i);
				db.insert("Category", null, values);
			}
		}

		@Override
		public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
			// TODO Auto-generated method stub
			db.execSQL("DROP TABLE IF EXISTS Category");
			db.execSQL("DROP TABLE IF EXISTS Contact");
			onCreate(db);
		}
		
	}
	
	public MyContactsDB(Context c){
		context = c;
	}
	
	public MyContactsDB open(){
		helper= new DbHelper(context);
		database = helper.getWritableDatabase();
		return this;
	}
	
	public void close(){
		helper.close();
	}
	
	public Category[] getAllCategories(){
		Cursor cursor = database.query("Category", new String[] {"Id","Name"}, null, null, null, null, null);
		Category[] categories = new Category[cursor.getCount()];
		int count = 0;
		while(cursor.moveToNext()){
			Category temp = new Category();
			temp.Id = cursor.getInt(cursor.getColumnIndex("Id"));
			temp.Name = cursor.getString(cursor.getColumnIndex("Name"));
			categories[count] = temp;
			count++;
		}
		return categories;
	}
	
	
	
	public Contact[] getAllContactsForCategory(int categoryId){
		Cursor cursor = database.query("Contact", new String[] {"Id","Name"}, "CategoryId=" + categoryId, null, null, null, "Name ASC");
		Contact[] contacts = new Contact[cursor.getCount()];
		int count = 0;
		while(cursor.moveToNext()){
			Contact temp = new Contact();
			temp.Id = cursor.getInt(cursor.getColumnIndex("Id"));
			temp.Name = cursor.getString(cursor.getColumnIndex("Name"));
			contacts[count] = temp;
			count++;
		}
		return contacts;
	}
	
	public Contact GetContactInformation(int contactId){
		Cursor cursor = database.query("Contact", new String[] {"Name", "Address", "CityZip", "CellPhone", "AltPhone", "Email", "Comments", "CategoryId"}, "Id="+contactId, null, null, null, null);
		Contact contact = new Contact();
		cursor.moveToFirst();
			Contact temp = new Contact();
			temp.Id = contactId;
			temp.Name = cursor.getString(cursor.getColumnIndex("Name"));
			temp.Address = cursor.getString(cursor.getColumnIndex("Address"));
			temp.CityZip = cursor.getString(cursor.getColumnIndex("CityZip"));
			temp.CellPhone = cursor.getString(cursor.getColumnIndex("CellPhone"));
			temp.AltPhone = cursor.getString(cursor.getColumnIndex("AltPhone"));
			temp.Email = cursor.getString(cursor.getColumnIndex("Email"));
			temp.Comments = cursor.getString(cursor.getColumnIndex("Comments"));
			temp.CategoryId = cursor.getInt(cursor.getColumnIndex("CategoryId"));
			contact = temp;
		
		return contact;
	}
	
	public Contact[] getAllContacts(){
		Cursor cursor = database.query("Contact", new String[] {"Id","Name", "Address", "CityZip", "CellPhone", "AltPhone", "Email", "Comments", "CategoryId"}, null, null, null, null, "Name ASC");
		Contact[] contacts = new Contact[cursor.getCount()];
		int count = 0;
		while(cursor.moveToNext()){
			Contact temp = new Contact();
			temp.Id = cursor.getInt(cursor.getColumnIndex("Id"));
			temp.Name = cursor.getString(cursor.getColumnIndex("Name"));
			temp.Address = cursor.getString(cursor.getColumnIndex("Address"));
			temp.CityZip = cursor.getString(cursor.getColumnIndex("CityZip"));
			temp.CellPhone = cursor.getString(cursor.getColumnIndex("CellPhone"));
			temp.AltPhone = cursor.getString(cursor.getColumnIndex("AltPhone"));
			temp.Email = cursor.getString(cursor.getColumnIndex("Email"));
			temp.Comments = cursor.getString(cursor.getColumnIndex("Comments"));
			temp.CategoryId = cursor.getInt(cursor.getColumnIndex("CategoryId"));
			contacts[count] = temp;
			count++;
		}
		return contacts;
	}
	
	public int insertContact(Contact contact){
		ContentValues values = new ContentValues();
		values.put("Name", contact.Name);
		values.put("Address", contact.Address);
		values.put("CityZip", contact.CityZip);
		values.put("CellPhone", contact.CellPhone);
		values.put("AltPhone", contact.AltPhone);
		values.put("Email", contact.Email);
		values.put("Comments", contact.Comments);
		values.put("CategoryId", contact.CategoryId);
		return (int)database.insert("Contact", null, values);
	}
	
	public int insertCategory(String name){
		ContentValues values = new ContentValues();
		values.put("Name", name);
		long i = database.insert("Category", null, values);
		return (int)i;
	}
	
	public void deleteCategory(int category){
		ContentValues values = new ContentValues();
		values.putNull("CategoryId");
		database.update("Contact", values, "CategoryId="+category, null);
		database.delete("Category", "Id ="+category, null);
	}
	
	public void RemoveContactFromCategory(int contactId){
		ContentValues values = new ContentValues();
		values.putNull("CategoryId");
		database.update("Contact", values, "Id="+contactId, null);
	}
	
	public void UpdateCategoryName(String name, int categoryId){
		ContentValues values = new ContentValues();
		values.put("Name", name);
		database.update("Category", values, "Id="+categoryId, null);
	}
	
	public void UpdateContactInformation(Contact contact){
		ContentValues values = new ContentValues();
		values.put("Name", contact.Name);
		values.put("Address", contact.Address);
		values.put("CityZip", contact.CityZip);
		values.put("CellPhone", contact.CellPhone);
		values.put("AltPhone", contact.AltPhone);
		values.put("Email", contact.Email);
		values.put("Comments", contact.Comments);
		values.put("CategoryId", contact.CategoryId);
		database.update("Contact", values, "Id="+contact.Id, null);
	}
	
	public void DeleteContact(int contactId){
		database.delete("Contact", "Id="+contactId, null);
	}
}
